"""
分类书籍查询类
使用原生SQL优化查询性能
"""

import aiomysql
from typing import List, Dict, Any, Optional
from db import DatabaseConnection, db_connection


class CategoryBookQuery:
    """分类书籍查询类，使用原生SQL优化性能"""

    def __init__(self):
        self.db_connection: DatabaseConnection = db_connection

    async def query_books_by_category(
        self,
        main_category: Optional[str] = None,
        sub_category: Optional[str] = None,
        page: int = 1,
        limit: int = 10,
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        按分类查询书籍，支持分页

        Args:
            main_category: 主分类名称
            sub_category: 子分类名称
            page: 页码，从1开始
            limit: 每页数量

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和书籍列表
            - int: 总书籍数量
            - List[Dict[str, Any]]: 书籍列表，每个字典包含图书信息及分类
        """
        offset = (page - 1) * limit

        # 构建查询条件
        params = []

        if main_category and sub_category:
            # 更简洁的直接JOIN方式
            sql = """
            SELECT DISTINCT b.*, 
                GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
            FROM books b
            INNER JOIN book_main_categories mc ON b.book_id = mc.book_id
            INNER JOIN book_sub_categories sc ON b.book_id = sc.book_id
            WHERE mc.main_category = %s AND sc.sub_category = %s
            GROUP BY b.book_id
            ORDER BY b.rating_count DESC, b.rating DESC, b.reading_count DESC, b.book_id ASC
            """
            count_sql = """
            SELECT COUNT(DISTINCT b.book_id) as total
            FROM books b
            INNER JOIN book_main_categories mc ON b.book_id = mc.book_id
            INNER JOIN book_sub_categories sc ON b.book_id = sc.book_id
            WHERE mc.main_category = %s AND sc.sub_category = %s
            """
            params = [main_category, sub_category]

        elif main_category:
            # 只查询主分类
            sql = """
            SELECT DISTINCT b.*, 
                   GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                   GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
            FROM books b
            LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
            LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
            WHERE b.book_id IN (
                SELECT DISTINCT book_id 
                FROM book_main_categories 
                WHERE main_category = %s
            )
            GROUP BY b.book_id
            ORDER BY b.rating_count DESC, b.rating DESC, b.reading_count DESC, b.book_id ASC
            """
            count_sql = """
            SELECT COUNT(DISTINCT book_id) as total
            FROM book_main_categories 
            WHERE main_category = %s
            """
            params = [main_category]

        elif sub_category:
            # 只查询子分类
            sql = """
            SELECT DISTINCT b.*, 
                   GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                   GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
            FROM books b
            LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
            LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
            WHERE b.book_id IN (
                SELECT DISTINCT book_id 
                FROM book_sub_categories 
                WHERE sub_category = %s
            )
            GROUP BY b.book_id
            ORDER BY b.rating_count DESC, b.rating DESC, b.reading_count DESC, b.book_id ASC
            """
            count_sql = """
            SELECT COUNT(DISTINCT book_id) as total
            FROM book_sub_categories 
            WHERE sub_category = %s
            """
            params = [sub_category]

        else:
            # 查询所有书籍
            sql = """
            SELECT DISTINCT b.*, 
                   GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
                   GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories
            FROM books b
            LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
            LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
            GROUP BY b.book_id
            ORDER BY b.rating_count DESC, b.rating DESC, b.reading_count DESC, b.book_id ASC
            """
            count_sql = "SELECT COUNT(*) as total FROM books"
            params = []

        # 添加分页
        sql += f" LIMIT {limit} OFFSET {offset}"

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 先查询总数
                    await cursor.execute(count_sql, params)
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询书籍数据
                    await cursor.execute(sql, params)
                    books = await cursor.fetchall()

                    # 处理分类数据
                    for book in books:
                        if book.get("main_categories"):
                            book["main_categories"] = book["main_categories"].split(",")
                        else:
                            book["main_categories"] = []

                        if book.get("sub_categories"):
                            book["sub_categories"] = book["sub_categories"].split(",")
                        else:
                            book["sub_categories"] = []

                    return total, books if books else []

        except Exception as e:
            print(f"分类查询失败: {e}")
            return 0, []

    async def get_all_main_categories(self) -> List[str]:
        """获取所有主分类"""
        sql = "SELECT DISTINCT main_category FROM book_main_categories ORDER BY main_category"

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    await cursor.execute(sql)
                    results = await cursor.fetchall()
                    return (
                        [item["main_category"] for item in results] if results else []
                    )
        except Exception as e:
            print(f"查询主分类失败: {e}")
            return []
